using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class PurchaseRequisitionDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procPurchaseRequisition_add]";
        private string _strSPInsertName1 = "dbo.[procPurchaseRequisition1_add]";
        private string _strSPUpdateName = "dbo.[procPurchaseRequisition_update]";
        private string _strSPUpdateName1 = "dbo.[procPurchaseRequisition1_update]";
        private string _strSPDeleteName = "dbo.[procPurchaseRequisition_delete]";
        private string _strSPDeleteName1 = "dbo.[procPurchaseRequisition1_delete]";
        private string _strSPGetName = "dbo.[procPurchaseRequisition_get]";
        private string _strSPGetAllName = "dbo.[procPurchaseRequisition_getall]";
        private string _strSPGetPages = "dbo.[procPurchaseRequisition_getpaged]";
        private string _strSPIsExist = "dbo.[procPurchaseRequisition_isexist]";
        private string _strSPIsExistReqRef = "dbo.[procPurchaseRequisition_isexistReqRef]";
        private string _strSPIsExist1 = "dbo.[procPurchaseRequisition1_isexist]";
        private string _strTableName = "PurchaseRequisition";
        private string _strSPGetCondName = "dbo.[procPurchaseRequisition_getCond]";
        private string _strSPGetCondName1 = "dbo.[procPurchaseRequisition1_getCond]";
        private string _strSPIsExistBudgetShared = "dbo.[procContr_isexistBudgetShared]";
        private string _strSPIsExistBudgetShared1 = "dbo.[procContr_isexistBudgetShared1]";
        private string _strSPGetRate = "dbo.[procPurchaseRequisition_GetRate]";
        private string _strSPGetRate1 = "dbo.[procPurchaseRequisition1_GetRate]";
        private string _strSPGetSumAmt = "dbo.[procPurchaseRequisition_SumAmt]";
        private string _strSPGetSumAmt1 = "dbo.[procPurchaseRequisition1_SumAmt]";
        #endregion Local Variable

        #region Method
        public PurchaseRequisitionInfo Get_PurchaseRequisition(
            Int32 Reference
        , ref string sErr)
        {
            PurchaseRequisitionInfo objEntr = new PurchaseRequisitionInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("Reference", Reference);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = (PurchaseRequisitionInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }



        public DataTable GetAll_PurchaseRequisition(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_PurchaseRequisition(PurchaseRequisitionInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("LineNo", objEntr.LineNo);
            AddParameter("CvtRate", objEntr.CvtRate);
            AddParameter("ReturnRef", objEntr.ReturnRef);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("RealContract", objEntr.RealContract);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            AddParameter("ModifiedDate", objEntr.ModifiedDate);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public Int32 Add_PurchaseRequisition1(PurchaseRequisitionInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName1);
            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("LineNo", objEntr.LineNo);
            AddParameter("CvtRate", objEntr.CvtRate);
            AddParameter("ReturnRef", objEntr.ReturnRef);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("RealContract", objEntr.RealContract);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            AddParameter("ModifiedDate", objEntr.ModifiedDate);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_PurchaseRequisition(PurchaseRequisitionInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("LineNo", objEntr.LineNo);
            AddParameter("CvtRate", objEntr.CvtRate);
            AddParameter("ReturnRef", objEntr.ReturnRef);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("RealContract", objEntr.RealContract);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            AddParameter("ModifiedDate", objEntr.ModifiedDate);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Update_PurchaseRequisition1(PurchaseRequisitionInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName1);

            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("LineNo", objEntr.LineNo);
            AddParameter("CvtRate", objEntr.CvtRate);
            AddParameter("ReturnRef", objEntr.ReturnRef);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("RealContract", objEntr.RealContract);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            AddParameter("ModifiedDate", objEntr.ModifiedDate);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_PurchaseRequisition(
            Int32 Reference
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("Reference", Reference);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_PurchaseRequisition1(
            Int32 Reference
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName1);
            AddParameter("Reference", Reference);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }


        public Boolean IsExist_PurchaseRequisition(
            Int32 Reference
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("Reference", Reference);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_ReqRef(
            Int32 Reference, String InvNo
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistReqRef);

            AddParameter("Reference", Reference);
            AddParameter("InvNo", InvNo);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_PurchaseRequisition1(
            Int32 Reference
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist1);

            AddParameter("Reference", Reference);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(PurchaseRequisitionInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetCond(string strCond, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT     InvNo, Reference, PymtRef, aYear, IssueDate, Contract, Department, Description, VendorCode, Creditor, Receiver, Mark, Amt, OtherAmt, Currency, CvtRate, Remark1, 
                      Remark2, Remark3, RealContract, OurRef, ReceiptDate, PaidDate, PaymentTerm, DueDate, DeliveryDate, ReturnedDate, TransMitRef, TransMitDate, MarkTrm, 
                      TransMitStat, [LineNo], ReturnRef, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate
FROM         PurchaseRequisition
";
            if (strCond != "") sql += " WHERE " + strCond + " ";
            sql += " ORDER BY [InvNo], [LineNo] ";
            InitCommand(sql, CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetCond1(string strCond, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT     InvNo, Reference, PymtRef, aYear, IssueDate, Contract, Department, Description, VendorCode, Creditor, Receiver, Mark, Amt, OtherAmt, Currency, CvtRate, Remark1, 
                      Remark2, Remark3, RealContract, OurRef, ReceiptDate, PaidDate, PaymentTerm, DueDate, DeliveryDate, ReturnedDate, TransMitRef, TransMitDate, MarkTrm, 
                      TransMitStat, [LineNo], ReturnRef, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate
FROM         PurchaseRequisition1
";
            if (strCond != "") sql += " WHERE " + strCond + " ";
            sql += " ORDER BY [InvNo], [LineNo] ";
            InitCommand(sql, CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public Boolean IsExist_BudgetShared(
            String Contract,
            Int32 aYear
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistBudgetShared);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_BudgetShared1(
           String Contract,
           Int32 aYear
       )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistBudgetShared1);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public DataTable GetRate(String Currency, DateTime IssueDate, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetRate);
            AddParameter("Currency", Currency);
            AddParameter("IssueDate", IssueDate);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetRate1(String Currency, DateTime IssueDate, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetRate1);
            AddParameter("Currency", Currency);
            AddParameter("IssueDate", IssueDate);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetSumAmt(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetSumAmt);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetSumAmt1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetSumAmt1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public bool IsExist_ReqRefHL(string refer, int year, int sysRef = 0)
        {
            var sql = @"SELECT  TOP 1 InvNo
                FROM         PurchaseRequisition
                WHERE RTRIM(@InvNo) = RTRIM(InvNo) and @aYear = aYear ";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            if (sysRef > 0) sql += " AND Reference <> @Reference ";
            AddParameter("InvNo", refer);
            AddParameter("aYear", year);
            if (sysRef > 0) AddParameter("Reference", year);
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list.Rows.Count > 0;
        }

        public bool IsExist_ReqRefHV(string refer, int year, int sysRef = 0)
        {
            var sql = @"SELECT  TOP 1  InvNo
                FROM         PurchaseRequisition1
                WHERE RTRIM(@InvNo) = RTRIM(InvNo) and @aYear = aYear ";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            if (sysRef > 0) sql += " AND Reference <> @Reference ";
            AddParameter("InvNo", refer);
            AddParameter("aYear", year);
            if (sysRef > 0) AddParameter("Reference", year);
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list.Rows.Count > 0;
        }

        public decimal getConvRateHL(DateTime dateTime, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT TOP 1 CvtRate
                FROM  PurchaseRequisition
                WHERE 	(YEAR(ReceiptDate)*1000 + MONTH(ReceiptDate))= @Period
                ORDER BY ReceiptDate DESC, Reference DESC";
            InitCommand(sql, CommandType.Text);

            AddParameter("Period", dateTime.Year * 1000 + dateTime.Month);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getConvRateHV(DateTime dateTime, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT TOP 1 CvtRate
                FROM  PurchaseRequisition1
                WHERE 	(YEAR(ReceiptDate)*1000 + MONTH(ReceiptDate))= @Period
                ORDER BY ReceiptDate DESC, Reference DESC";
            InitCommand(sql, CommandType.Text);

            AddParameter("Period", dateTime.Year * 1000 + dateTime.Month);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public DataTable getAmtsHL(string refer, int year, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT  TOP 1    Reference, Amt, OtherAmt
                FROM         PurchaseRequisition
                WHERE @InvNo = InvNo and @aYear = aYear", CommandType.Text);

            AddParameter("InvNo", refer);
            AddParameter("aYear", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable getAmtsHV(string refer, int year, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT  TOP 1    Reference, Amt, OtherAmt
                FROM         PurchaseRequisition1
                WHERE @InvNo = InvNo and @aYear = aYear", CommandType.Text);

            AddParameter("InvNo", refer);
            AddParameter("aYear", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable getOtherPurchaseByRefHL(string refer, int outYear, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Reference, aYear, Amt, OtherAmt, 0 AS OReference
                            FROM          PurchaseRequisition
                            WHERE      (InvNo = @InvNo) AND (aYear <> @aYear)", CommandType.Text);

            AddParameter("InvNo", refer);
            AddParameter("aYear", outYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable getOtherPurchaseByRefHV(string refer, int outYear, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Reference,aYear, Amt, OtherAmt, 0 AS OReference
                            FROM          PurchaseRequisition1
                            WHERE      (InvNo = @InvNo) AND (aYear <> @aYear)", CommandType.Text);

            AddParameter("InvNo", refer);
            AddParameter("aYear", outYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public string deleteRefHL(string refer, int year)
        {
            openConnection();
            InitCommand(@"DELETE FROM PurchaseRequisition
                WHERE @InvNo = InvNo and @aYear = aYear", CommandType.Text);
            AddParameter("InvNo", refer);
            AddParameter("aYear", year);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string deleteRefHV(string refer, int year)
        {
            openConnection();
            InitCommand(@"DELETE FROM PurchaseRequisition1
                WHERE @InvNo = InvNo and @aYear = aYear", CommandType.Text);
            AddParameter("InvNo", refer);
            AddParameter("aYear", year);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string updateAmtHL(PurchaseRequisitionInfo info)
        {
            var sql = @"UPDATE PurchaseRequisition SET 
                    InvNo   = @InvNo,
                    Amt         = @Amt,
                    OtherAmt    = @OtherAmt,
                    CvtRate     = @CvtRate
                WHERE Reference   = @Reference";
            string sErr = "";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", info.Reference);
            AddParameter("InvNo", info.InvNo);            
            AddParameter("Amt", info.Amt);
            AddParameter("OtherAmt", info.OtherAmt);
            AddParameter("CvtRate", info.CvtRate);

            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public string updateAmtHV(PurchaseRequisitionInfo info)
        {
            var sql = @"UPDATE PurchaseRequisition1 SET 
                    InvNo   = @InvNo,
                    Amt         = @Amt,
                    OtherAmt    = @OtherAmt,
                    CvtRate     = @CvtRate
                WHERE Reference   = @Reference";
            string sErr = "";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", info.Reference);
            AddParameter("InvNo", info.InvNo);
            AddParameter("Amt", info.Amt);
            AddParameter("OtherAmt", info.OtherAmt);
            AddParameter("CvtRate", info.CvtRate);

            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public DataTable GetShortAllHL(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     InvNo
                FROM         PurchaseRequisition
                GROUP BY InvNo", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable GetShortAllHV(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     InvNo
                FROM         PurchaseRequisition1
                GROUP BY InvNo", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public decimal getBudgetTotalAmtHL(string code, int year, ref string sErr, int refer)
        {
            decimal ret = 0;
            openConnection();
            InitCommand(@"SELECT     SUM(Amt) as Amt
                FROM         PurchaseRequisition
                WHERE aYear = @aYear  AND Contract = @Contract AND Reference <> @Reference ", CommandType.Text);
            AddParameter("aYear", year);
            AddParameter("Contract", code);
            AddParameter("Reference", refer);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) ret = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        public decimal getBudgetTotalAmtHV(string code, int year, ref string sErr, int refer)
        {
            decimal ret = 0;
            openConnection();
            InitCommand(@"SELECT     SUM(Amt) as Amt
                FROM         PurchaseRequisition1
                WHERE  aYear = @aYear AND Contract = @Contract AND Reference <> @Reference", CommandType.Text);
            AddParameter("aYear", year);
            AddParameter("Contract", code);
            AddParameter("Reference", refer);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) ret = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }





        public decimal getBudgetGroupTotalAmtHL(string code, int year, ref string sErr, int refer)
        {
            decimal ret = 0;
            openConnection();
            InitCommand(@"DECLARE @group AS nvarchar(15)
                SET @group = (SELECT BudgetGroup FROM Budgets WHERE Contract = @Contract AND aYear = @aYear)
		
                SELECT     SUM(PurchaseRequisition.Amt) AS Amt
                FROM         PurchaseRequisition 
	                INNER JOIN	Budgets ON PurchaseRequisition.Contract = Budgets.Contract AND PurchaseRequisition.aYear = Budgets.aYear
                WHERE     (PurchaseRequisition.aYear = @aYear) AND (PurchaseRequisition.Reference <> @Reference) AND (Budgets.BudgetGroup = @group)
                GROUP BY Budgets.BudgetGroup", CommandType.Text);
            AddParameter("aYear", year);
            AddParameter("Contract", code);
            AddParameter("Reference", refer);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) ret = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getBudgetGroupTotalAmtHV(string code, int year, ref string sErr, int refer)
        {
            decimal ret = 0;
            openConnection();
            InitCommand(@"DECLARE @group AS nvarchar(15)
                SET @group = (SELECT BudgetGroup FROM Budgets1 WHERE Contract = @Contract AND aYear = @aYear)
		
                SELECT     SUM(PurchaseRequisition1.Amt) AS Amt
                FROM         PurchaseRequisition1 
	                INNER JOIN	Budgets1 ON PurchaseRequisition1.Contract = Budgets1.Contract AND PurchaseRequisition1.aYear = Budgets1.aYear
                WHERE     (PurchaseRequisition1.aYear = @aYear) AND (PurchaseRequisition1.Reference <> @Reference) AND (Budgets1.BudgetGroup = @group)
                GROUP BY Budgets1.BudgetGroup", CommandType.Text);
            AddParameter("aYear", year);
            AddParameter("Contract", code);
            AddParameter("Reference", refer);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) ret = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
    }
}
